Unions and Combining Datasets in Tableau

Course: Tableau Training

Topic: Unions and Combining Datasets

Objective: Learn how to combine multiple datasets in Tableau using unions, understand the difference between unions, joins, relationships, and data blending, and prepare consolidated data for effective business analysis.

Course Links: Explore Tableau Training | Register for a Tableau Course Demo

1. Introduction to Combining Datasets

In real-world business environments, data is frequently stored in multiple files, worksheets, tables, databases, or systems. An organization may maintain separate sales records for each month, customer information in another file, and product details in a separate database. Analyzing each dataset independently may make it difficult to understand the overall business performance.

Tableau provides several methods for combining data so that analysts can create comprehensive reports and dashboards. The appropriate method depends on the structure of the datasets, the relationship between their fields, and the business question being investigated.

For example, a company may maintain sales records in three Excel worksheets: January Sales, February Sales, and March Sales. If all three worksheets contain similar columns, Tableau can combine their rows into one dataset using a union.

Why Is Combining Data Important?

• Consolidation: Combines information stored across multiple files or tables into a usable analytical dataset.
• Comprehensive analysis: Allows users to analyze data across multiple months, quarters, regions, or business units.
• Improved reporting: Supports consolidated dashboards and management reports.
• Historical comparisons: Makes it easier to compare current performance with previous periods.
• Reduced manual work: Avoids manually copying and pasting rows from multiple worksheets.
• Better decision-making: Helps businesses identify trends, opportunities, and performance gaps across datasets.

2. What Is a Union in Tableau?

A union is a data-combination operation that appends rows from one table to another. The resulting dataset contains the records from the participating tables in a single table structure.

Unions are most useful when datasets contain similar types of information and have compatible columns, such as monthly sales records, yearly transaction files, or regional sales worksheets.

Example: Combining Monthly Sales Data

Suppose an organization maintains the following datasets.

January Sales

February Sales

After creating a union, Tableau produces one combined dataset.

Combined Sales Dataset

The result contains four rows instead of two separate tables. The union appends the records vertically while retaining the shared column structure.

Key Characteristics of a Union

• Combines data vertically by appending rows.
• Works best when the datasets have compatible fields and data types.
• Matches corresponding fields primarily by field name, not merely by their displayed position.
• Can combine multiple tables or worksheets supported by the same connection.
• Can introduce additional fields or null values when column names or structures differ.
• Does not automatically remove duplicate records.
• Can provide metadata fields that identify the source table or worksheet of each record.

3. Understanding Vertical and Horizontal Data Combination

Before working with unions, it is important to understand the two common ways to combine tables.

Vertical Combination

Vertical combination adds rows to an existing dataset. It is the purpose of a union.

Table A:
Order ID | Product | Sales
101      | Laptop  | 50000
102      | Mobile  | 20000

Table B:
Order ID | Product | Sales
103      | Tablet  | 30000

Union Result:
Order ID | Product | Sales
101      | Laptop  | 50000
102      | Mobile  | 20000
103      | Tablet  | 30000

Horizontal Combination

Horizontal combination adds columns by matching related records between tables. A join is a common method for performing this operation.

Orders:
Order ID | Customer ID | Sales
101      | C01         | 50000

Customers:
Customer ID | Customer Name
C01         | Rahul

Joined Result:
Order ID | Customer ID | Sales | Customer Name
101      | C01         | 50000 | Rahul

Remember: A union adds rows, while a join adds columns by matching records.

4. Requirements for Creating a Union

Before creating a union in Tableau, review the structure and compatibility of the source tables.

4.1 Compatible Columns

The tables should contain corresponding columns that represent the same business concepts. For example, Sales, Product, Order Date, and Region should represent consistent information across the source tables.

4.2 Matching Field Names

Tableau normally matches corresponding fields by their names. If one table contains Customer Name and another contains Customer, Tableau may treat these as separate fields, producing null values in portions of the combined data.

4.3 Compatible Data Types

Corresponding fields should have compatible data types. For example, Sales should consistently be numeric, Order Date should be a date, and Product should generally be text.

4.4 Same Connection

In Tableau's standard data-source union workflow, tables must come from the same connection. If the datasets are stored in different databases or systems, consider other supported data-combination methods or prepare the data using Tableau Prep or another ETL tool.

4.5 Consistent Meaning

Matching column names alone are not enough. A field named Amount in one file might represent revenue, while Amount in another might represent tax. Confirm that the fields have the same business meaning before combining them.

5. Step-by-Step: Creating a Union in Tableau

The following procedure explains how to create a union from worksheets or tables available within a supported connection.

Step 1: Prepare the Source Files

Create two or more datasets with compatible columns. For example, prepare January_Sales.xlsx, February_Sales.xlsx, and March_Sales.xlsx, each containing Order ID, Order Date, Product, Region, and Sales.

Step 2: Open Tableau

Launch Tableau Desktop or an appropriate Tableau authoring environment. From the Connect pane, select the supported data source, such as Microsoft Excel or a text file.

Step 3: Connect to the Dataset

Select the workbook or file containing the tables or worksheets you want to combine. Open the Data Source page and inspect the available tables.

Step 4: Create a New Union

In supported connections, locate New Union in the left pane or table area. Double-click it to open the union configuration dialog.

Step 5: Add the First Table

Drag the first worksheet or table into the union dialog. This becomes the first input to the union.

Step 6: Add Additional Tables

Drag the next table directly below the first table. Repeat this process for all remaining tables you want to combine. Depending on the interface, you may select multiple tables together before dragging them.

Step 7: Apply the Union

Click Apply or OK, as available in your Tableau interface. Tableau combines the selected rows into a single table.

Step 8: Inspect the Combined Data

Review the data grid and verify the following:

• Expected columns appear in the result.
• Records from every source table are included.
• Corresponding fields have been matched correctly.
• Data types are appropriate.
• Unexpected null values and duplicate records are investigated.
• Source-table metadata is available where supported.

Step 9: Build a Worksheet

Open a worksheet and create a simple chart. For example, place Order Date on Columns, Sales on Rows, and Product on Color to examine sales trends across the combined data.

Step 10: Save the Workbook

Save the Tableau workbook after verifying the results. If the underlying data changes, refresh the connection or extract as appropriate.

6. Manual Union in Tableau

A manual union is useful when you know which tables must be combined and want to select them individually.

Example Scenario

A retail company maintains three worksheets: North_Sales, South_Sales, and West_Sales. Each worksheet contains Order ID, Product, Sales, and Region.

Procedure:

1. Connect Tableau to the workbook containing the three worksheets.
2. Open the Data Source page.
3. Double-click New Union.
4. Drag North_Sales into the union dialog.
5. Drag South_Sales below North_Sales.
6. Drag West_Sales below South_Sales.
7. Apply the union.
8. Inspect the resulting rows and fields.

The result is one dataset containing records from all three regions. You can use Region to compare regional sales, provided the field values are populated correctly.

7. Wildcard Union in Tableau

A wildcard union allows Tableau to find and combine tables or worksheets automatically when their names match a specified text pattern. It is useful when new files or worksheets are added regularly and follow a consistent naming convention.

Example Scenario

A company stores monthly worksheets named Sales_January, Sales_February, Sales_March, and Sales_April. Instead of selecting every worksheet individually, a wildcard union can search for names matching a pattern such as Sales_* when supported by the selected connection.

Steps to Create a Wildcard Union

1. Connect to the relevant supported data source.
2. Open the Data Source page.
3. Double-click New Union.
4. Choose the wildcard or automatic table-matching option in the union dialog.
5. Enter a matching pattern, such as Sales_* or another pattern appropriate to the file naming convention.
6. Review the matching tables or worksheets.
7. Apply the union and validate the resulting data.

Advantages of Wildcard Union

• Reduces repetitive manual selection.
• Makes recurring monthly or quarterly reporting easier.
• Can include additional matching worksheets or files when the connection and matching configuration support them.
• Helps standardize recurring data consolidation tasks.

Important Considerations

• Use a naming convention that distinguishes relevant files from unrelated ones.
• Confirm which files or worksheets are included by the pattern.
• Ensure newly added files follow the expected schema.
• Refresh the data and verify that new records appear as expected.
• Wildcard capabilities vary by connector and Tableau environment.

8. Understanding Field Name Mismatches

One of the most common union problems occurs when corresponding columns have different names.

Example

Suppose the January dataset uses Customer Name, while the February dataset uses Customer.

January Dataset

February Dataset

If Tableau treats Customer Name and Customer as different fields, the union may produce a result like this:

How to Fix Field Name Mismatches

1. Rename the source fields so that they use consistent names before creating the union, where possible.
2. Use Tableau's available merge-field functionality to combine corresponding nonmatching fields when supported.
3. Confirm that the fields have compatible data types.
4. Inspect the resulting field and verify that all expected customer values are present.
5. If the source files cannot be modified, use a supported data-preparation workflow to standardize the schema.

Important: Do not merge fields merely because their names appear similar. Confirm that they represent the same information.

9. Handling Missing Fields and Null Values

Null values may appear in a union when a field exists in one table but is absent from another, or when corresponding fields have different names. Null values can also originate from missing values in the original source data.

Example

The January file contains Order ID, Product, and Sales. The February file contains Order ID, Product, Sales, and Discount. The combined data may include a Discount field with null values for records originating from January.

Ways to Handle Missing Values

• Standardize source schemas before combining datasets.
• Investigate whether null values mean missing information, not applicable, or an actual zero.
• Merge equivalent fields when appropriate and supported by the workflow.
• Use calculated fields to handle nulls where the business rules justify it.
• Use Tableau Prep for more complex field cleaning and data standardization.

For example, the following calculated field replaces null Discount values with zero when a missing discount genuinely means no discount was offered.

IFNULL([Discount], 0)

This calculation should only be used when zero is the correct business interpretation. A missing value does not always mean zero.

10. Identifying the Source of Each Record

When several datasets are combined, it can be useful to know which source file, table, or worksheet contributed each record. Tableau can generate union metadata fields, such as Sheet or Table Name, depending on the connector and data source.

Why Source Metadata Matters

• Identifies the original worksheet or table for a record.
• Helps compare records from different reporting periods.
• Supports troubleshooting when a source contains incorrect data.
• Can help derive month, year, region, or department information when that information is encoded in the source name.

Example

Suppose the union combines worksheets named January_Sales, February_Sales, and March_Sales. If Tableau exposes a source-table metadata field, you can use it to distinguish the records by their originating worksheet.

For reliable analysis, an explicit Order Date or Reporting Month field is generally preferable to relying exclusively on worksheet names. Metadata can be helpful when the source does not already contain the required information.

11. Combining Datasets in Tableau: Overview

Tableau provides multiple ways to combine data. A union is only one of these methods. Selecting the correct method is important because each technique produces a different analytical result.

11.1 Unions

Unions append rows from compatible tables. They are appropriate when datasets share a similar structure and represent additional records of the same general type.

Example: Combining January, February, and March sales records.

11.2 Joins

Joins combine tables horizontally using matching fields and a specified join type. Common join types include inner, left, right, and full outer joins.

Example: Combining order transactions with customer details using Customer ID.

11.3 Relationships

Relationships connect logical tables through related fields while allowing Tableau to determine how to query them based on the visualization. Relationships are the recommended starting point for many analyses involving multiple tables, particularly when tables have different levels of detail.

Example: Relating Orders to Customers using Customer ID without immediately flattening both tables into a single physical table.

11.4 Data Blending

Data blending combines aggregated results from separate data sources in a visualization. The underlying sources remain separate, and the relationship is established for a worksheet using linking fields.

Example: Comparing sales from one data source with target figures from another source when a direct join or relationship is unsuitable.

12. Union vs Join vs Relationship vs Data Blending

Selection rule: Use a union to add rows, a join to add columns through matching records, a relationship to connect tables flexibly for analysis, and blending when separately queried data sources need to be visualized together.

13. Practical Example: Combining Quarterly Sales Data

Consider a company that stores sales transactions in separate files for four quarters. Each file contains Order ID, Order Date, Product, Region, and Sales.

Business Objective

Create a Tableau dashboard showing total yearly sales, quarterly performance, product performance, and regional trends.

Step 1: Prepare the Data

Check that all four files contain the same fields and that the fields use consistent names and data types.

Step 2: Create the Union

Connect Tableau to the supported source and union the four quarterly tables. If the files follow a consistent naming convention and the connector supports it, consider a wildcard union.

Step 3: Validate the Combined Dataset

Compare the number of records in the union with the expected total from all source files. Review missing values, duplicated transactions, and date ranges.

Step 4: Create a Total Sales Worksheet

1. Open a worksheet.
2. Drag Sales to Rows.
3. Confirm that the aggregation is SUM(Sales).
4. Format the result as currency if appropriate.

Step 5: Create a Quarterly Sales Chart

1. Drag Order Date to Columns.
2. Choose a suitable date level, such as Quarter.
3. Drag Sales to Rows.
4. Choose a bar chart or line chart based on the analytical goal.
5. Use Year and Quarter appropriately if the data spans multiple years.

Step 6: Analyze Product Performance

1. Open a new worksheet.
2. Place Product on Rows.
3. Place Sales on Columns.
4. Sort products by sales in descending order.
5. Use labels or tooltips to display sales values.

Step 7: Analyze Regional Performance

Place Region on Rows and SUM(Sales) on Columns. Use color to distinguish categories or create a geographic visualization when appropriate location fields are available.

Step 8: Build a Dashboard

Combine the quarterly sales trend, product performance, regional analysis, and key performance indicators into one dashboard. Add appropriate filters, titles, and tooltips to help users explore the results.

Expected Business Outcomes

• Understand the overall sales performance across the year.
• Identify the best-performing quarter.
• Determine which products generate the highest revenue.
• Compare sales across regions.
• Identify periods of declining or increasing sales.

14. Best Practices for Unions in Tableau

• Standardize schemas: Keep column names, data types, and business definitions consistent across source tables.
• Validate record counts: Compare the combined record count with the expected total.
• Check duplicates: A union appends records and does not automatically remove duplicate rows.
• Preserve source information: Retain useful source metadata or explicit reporting-period fields.
• Use meaningful field names: Avoid vague or inconsistent column names.
• Verify date fields: Confirm that dates are recognized correctly and cover the intended period.
• Investigate null values: Determine why values are missing before replacing them.
• Choose the right combination method: Do not use a union when the real requirement is to match records and add columns.
• Monitor new files: If using wildcard union, verify that newly added files are included as intended.
• Use Tableau Prep for complex preparation: Consider it for repeated schema changes, advanced cleaning, aggregation, and more involved workflows.
• Document transformations: Record which files were included and any cleaning rules applied.
• Test dashboard results: Reconcile important totals against the original sources before sharing reports.

15. Common Union Problems and Solutions

16. Union and Data Quality Validation

Data validation is essential because a union may succeed technically even when the resulting data is incomplete or inconsistent.

Validation Checklist

• Confirm that all intended source tables are included.
• Check the total number of records in each source and the union.
• Check for duplicated business keys such as Order ID or Transaction ID.
• Review null counts in important fields.
• Verify date ranges and reporting periods.
• Compare important totals against source-system totals.
• Confirm that field types and units are consistent.
• Test key dashboard calculations against a small manually verified sample.

Important: The sum of the source row counts should generally match the union row count before any subsequent filtering or transformation, assuming every source row is included and no rows are removed. Duplicate rows can still be present in that total.

17. Business Use Cases of Unions

17.1 Monthly Sales Reporting

Combine monthly sales files to create a consolidated report showing total revenue, sales growth, and monthly performance.

17.2 Yearly Financial Analysis

Append financial records from multiple periods to compare income, expenses, and profitability across years.

17.3 Regional Reporting

Combine regional datasets with a consistent structure to compare business performance across locations.

17.4 Customer Transaction Analysis

Append transaction files from different periods to understand purchasing frequency and sales patterns.

17.5 Inventory Monitoring

Combine periodic inventory snapshots when the files have compatible structures, allowing analysts to examine stock levels across reporting dates.

17.6 Employee Reporting

Combine departmental employee datasets when the same fields and definitions are used across departments.

18. Hands-On Practice Exercise

Exercise: Create a consolidated sales dataset from three tables.

Dataset A: January

Dataset B: February

Dataset C: March

Tasks

1. Import the three datasets into a supported Tableau connection.
2. Create a union containing all three tables.
3. Verify that the resulting dataset contains six records.
4. Create a bar chart showing total sales by product.
5. Create a line chart showing sales over time after ensuring the dataset includes a valid date or month field.
6. Create a regional sales comparison.
7. Build a dashboard containing the three visualizations.
8. Check the totals against the original source tables.

Expected Product Sales

Note: The exercise tables do not include a date field. Add a proper Order Date or Reporting Month field before creating the time-series chart. Do not infer exact transaction dates from the month names alone.

19. Tableau Union Interview Questions and Answers

Q1. What is a union in Tableau?

Answer: A union combines two or more tables by appending their rows into a single dataset. It is best suited to tables with compatible structures.

Q2. What is the difference between a union and a join?

Answer: A union adds rows vertically, whereas a join adds columns horizontally by matching records through specified join conditions.

Q3. When should you use a union?

Answer: Use a union when datasets represent similar types of records and need to be consolidated, such as monthly transaction files or regional sales worksheets.

Q4. What is a wildcard union?

Answer: A wildcard union automatically identifies and combines supported tables or worksheets whose names match a configured text pattern.

Q5. Does a union remove duplicate rows automatically?

Answer: No. A union appends records and does not automatically deduplicate them. Duplicate handling must be performed separately when necessary.

Q6. What happens when field names differ between unioned tables?

Answer: Tableau may treat differently named fields as separate columns, resulting in null values. Standardizing or merging corresponding fields can resolve the issue.

Q7. Can tables with different numbers of columns be unioned?

Answer: Tables with different schemas can sometimes be combined, but unmatched fields may produce additional columns and null values. Standardizing the schema is generally preferable.

Q8. What is the purpose of union metadata fields?

Answer: They help identify the source table or worksheet from which each record originated, supporting validation and source-level analysis.

Q9. What is the difference between a union and a relationship?

Answer: A union appends rows into a combined table, while a relationship connects logical tables using related fields and allows Tableau to query them according to the analysis.

Q10. Can you union files from different databases directly?

Answer: The standard Tableau union workflow requires tables to be from the same connection. For different systems, use a supported preparation workflow or another appropriate combination method.

Q11. Why might a union produce incorrect sales totals?

Answer: Common causes include duplicate records, overlapping source files, inconsistent schemas, or incorrect field interpretation. Validate source totals and record identifiers.

Q12. When should Tableau Prep be considered?

Answer: Tableau Prep is useful when combining datasets requires substantial cleaning, field standardization, aggregation, or more complex data-preparation steps.

20. Quick Revision Notes

• A union combines rows vertically.
• A join combines columns horizontally through matching conditions.
• Corresponding fields in a union should have consistent names, meanings, and compatible data types.
• Manual unions are useful when selecting specific tables individually.
• Wildcard unions can simplify recurring consolidation when supported.
• Different field names can create separate columns and null values.
• Union metadata can help identify each record's source.
• A union does not automatically remove duplicate records.
• Relationships are often the preferred approach for connecting tables with different levels of detail.
• Validate row counts, null values, data types, duplicates, and business totals before publishing a dashboard.

21. Final Conclusion

Unions and dataset-combination techniques are important skills for Tableau developers and data analysts. A union is especially valuable when similar datasets need to be consolidated into one table, such as monthly sales files, quarterly reports, or regional records. Successful union operations depend on compatible field structures, consistent data types, and careful validation.

Understanding the differences between unions, joins, relationships, and data blending helps analysts select the right method for each business requirement. After combining the data, users can create meaningful worksheets, interactive dashboards, and reports that support informed decision-making.

Continue Learning: Explore Tableau Training | Register for a Tableau Course Demo

22. Additional Learning Resources

• Official Tableau Documentation: Union Your Data
• Official Tableau Documentation: Plan the Data Source and Combine Data
• Official Tableau Documentation: Join Your Data
• Official Tableau Prep Documentation: Aggregate, Join, or Union Data